/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Helper.BillsHepler;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author TrongChien
 */
public class BillsDAO {

    public static int InsertBills(Connection cnn, BillsHepler obj) throws Exception {
        String strSQL = "INSERT INTO tblBills(UId, CID, Date) VALUES(?,?,GETDATE())";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getUserId());
        stmt.setString(2, obj.getCusId());
        return stmt.executeUpdate();
    }

    public static int InsertBillsDetail(Connection cnn, BillsHepler obj) throws Exception {
        String strSQL = "INSERT INTO tblBillsDetail(BID, IID, Quantity, Price) VALUES(?,?,?,?)";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        stmt.setString(2, obj.getItemId());
        stmt.setString(3, obj.getQuantity());
        stmt.setString(4, obj.getPrice());
        return stmt.executeUpdate();
    }

    public static ResultSet FindOne(Connection cnn) throws Exception {
        String strSQL = "SELECT TOP 1 * FROM tblBills ORDER BY BID DESC";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        return stmt.executeQuery();
    }

    public static ResultSet FindOne(Connection cnn, BillsHepler obj) throws Exception {
        String strSQL = "SELECT * FROM tblBills t1, tblCustomer t2, tblUsers t3 WHERE t1.CID=t2.CID and t1.UID=t3.UID and t1.BID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        return stmt.executeQuery();
    }

    public static int UpdateBillDetail(Connection cnn, BillsHepler obj) throws Exception {
        String strSQL = "UPDATE tblBillsDetail SET Quantity=? WHERE BID=? and IID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getQuantity());
        stmt.setString(2, obj.getId());
        stmt.setString(3, obj.getItemId());
        return stmt.executeUpdate();
    }

    public static int UpdateBillsStatus(Connection cnn, BillsHepler obj) throws Exception {
        String strSQL = "UPDATE tblBills SET Status=? WHERE BID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, "1");
        stmt.setString(2, obj.getId());
        return stmt.executeUpdate();
    }

//    public static int Update(Connection cnn, BillsHepler obj) throws Exception{
//        String strSQL = "UPDATE tblBills SET Discount=?, VAT=?, PID=? WHERE BID=?";
//        PreparedStatement stmt = cnn.prepareStatement(strSQL);
//        stmt.setString(1, obj.getDiscount());
//        stmt.setString(2, obj.getVAT());
//        stmt.setString(3, obj.getPID());
//        stmt.setString(4, obj.getId());
//        return stmt.executeUpdate();
//    }
    public static int Update(Connection cnn, BillsHepler obj) throws Exception {
        double total = Double.parseDouble(obj.getTotal())  - Double.parseDouble(obj.getTotal()) * (Double.parseDouble(obj.getDiscount()) / 100) + Double.parseDouble(obj.getTotal())*(Double.parseDouble(obj.getVAT()) / 100);
        String strSQL = "UPDATE tblBills SET Discount=?, VAT=?, PID=?, Total=? WHERE BID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getDiscount());
        stmt.setString(2, obj.getVAT());
        stmt.setString(3, obj.getPID());
        stmt.setString(4, String.valueOf(total));
        stmt.setString(5, obj.getId());
//            System.out.println(obj.getDiscount());
//            System.out.println(obj.getVAT());
//            System.out.println(obj.getPID());
//            System.out.println(obj.getTotal());
//            System.out.println(obj.getId());
        return stmt.executeUpdate();
    }

    public static int DeleteBillsDetail(Connection cnn, String id) throws Exception {
        String strSQL = "DELETE FROM tblBillsDetail WHERE BID = ?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, id);
        return stmt.executeUpdate();
    }

    public static int DeleteBills(Connection cnn, String id) throws Exception {
        String strSQL = "UPDATE  tblBills SET Status='2' WHERE BID = ?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, id);
        return stmt.executeUpdate();
    }

    public static ArrayList<BillsHepler> FindAll(Connection cnn) throws Exception {
        ArrayList<BillsHepler> arrBillsList = new ArrayList<BillsHepler>();
        BillsHepler billsData = null;
        //String strSQL = "SELECT * FROM tblBills t1, tblCustomer t2 where t1.CID=t2.CID and t1.Status=0";
        String strSQL = "SELECT * FROM tblBills t1, tblCustomer t2,tblPayment t3 where t1.CID=t2.CID and  t1.PID=t3.PID and t1.Status=0";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            billsData = new BillsHepler();
            billsData.setId(rs.getString("BID"));
            billsData.setCusName(rs.getString("CName"));
            billsData.setDiscount(rs.getString("Discount"));
            billsData.setVAT(rs.getString("VAT"));
            billsData.setPID(rs.getString("PName"));
            arrBillsList.add(billsData);
        }
        return arrBillsList;
    }

    public static ArrayList<BillsHepler> FindAllOrder(Connection cnn) throws Exception {
        ArrayList<BillsHepler> arrOrderList = new ArrayList<BillsHepler>();
        BillsHepler orderData = null;
        String strSQL = "SELECT * FROM tblBills t1, tblCustomer t2, tblPayment t3,  tblBillsDetail t4, tblItems t5 "
                + "where t4.IID=t5.IID and t1.BID=t4.BID and t1.CID=t2.CID and  t1.PID=t3.PID and t1.Status=1";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            orderData = new BillsHepler();
            orderData.setId(rs.getString("BID"));
            orderData.setCusName(rs.getString("CName"));
            orderData.setItemName(rs.getString("IName"));
            orderData.setPID(rs.getString("PName"));
            orderData.setDate(rs.getString("Date"));
            orderData.setQuantity(rs.getString("Quantity"));
            orderData.setTotal(rs.getString("Total"));
            arrOrderList.add(orderData);
        }
        return arrOrderList;
    }
    public static ArrayList<BillsHepler> FindAllOrderCancel(Connection cnn) throws Exception {
        ArrayList<BillsHepler> arrOrderList = new ArrayList<BillsHepler>();
        BillsHepler orderData = null;
        String strSQL = "SELECT * FROM tblBills t1, tblCustomer t2, tblPayment t3,  tblBillsDetail t4, tblItems t5 "
                + "where t4.IID=t5.IID and t1.BID=t4.BID and t1.CID=t2.CID and  t1.PID=t3.PID and t1.Status=1";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            orderData = new BillsHepler();
            orderData.setId(rs.getString("BID"));
            orderData.setCusName(rs.getString("CName"));
            orderData.setItemName(rs.getString("IName"));
            orderData.setPID(rs.getString("PName"));
            orderData.setDate(rs.getString("Date"));
            orderData.setQuantity(rs.getString("Quantity"));
            orderData.setTotal(rs.getString("Total"));
            arrOrderList.add(orderData);
        }
        return arrOrderList;
    }
    public static ArrayList<BillsHepler> FindAllItemsReturned(Connection cnn) throws Exception {
        ArrayList<BillsHepler> arrOrderList = new ArrayList<BillsHepler>();
        BillsHepler orderData = null;
        String strSQL = "SELECT * FROM tblBills t1, tblCustomer t2, tblPayment t3,  tblBillsDetail t4, tblItems t5 "
                + "where t4.IID=t5.IID and t1.BID=t4.BID and t1.CID=t2.CID and  t1.PID=t3.PID and t1.Status=2";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            orderData = new BillsHepler();
            orderData.setId(rs.getString("BID"));
            orderData.setCusName(rs.getString("CName"));
            orderData.setItemName(rs.getString("IName"));
            orderData.setQuantity(rs.getString("Quantity"));
            orderData.setTotal(rs.getString("Total"));
            arrOrderList.add(orderData);
        }
        return arrOrderList;
    }

    public static ResultSet FindOneBills(Connection cnn, BillsHepler obj) throws Exception {
        String strSQL = "SELECT * FROM tblBills t1, tblCustomer t2 where t1.CID=t2.CID where t1.BID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        return stmt.executeQuery();
    }

    public static ArrayList<BillsHepler> FindItemBills(Connection cnn, BillsHepler obj) throws Exception {
        ArrayList<BillsHepler> arrBillsList = new ArrayList<BillsHepler>();
        BillsHepler billsData = null;
        String strSQL = "SELECT t1.IID, t1.IName, t2.Price, t2.Quantity FROM tblItems t1, tblBillsDetail t2 where t1.IId=t2.IId and t2.BID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            billsData = new BillsHepler();
            billsData.setId(rs.getString("IId"));
            billsData.setItemName(rs.getString("IName"));
            billsData.setPrice(rs.getString("Price"));
            billsData.setQuantity(rs.getString("Quantity"));
            int total = Integer.parseInt(rs.getString("Price")) * Integer.parseInt(rs.getString("Quantity"));
            billsData.setTotal(String.valueOf(total));
            arrBillsList.add(billsData);
        }
        return arrBillsList;
    }

    public static ResultSet FindItemBill(Connection cnn, BillsHepler obj) throws Exception {
        String strSQL = "SELECT t1.IID, t1.IName, t2.Price, t2.Quantity FROM tblItems t1, tblBillsDetail t2 where t1.IId=t2.IId and t2.BID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        return stmt.executeQuery();
    }
}
